using System;
using System.Collections;
using System.Collections.Generic;
using System.Reflection;
using System.Data;
using System.Text;

namespace Dbam.DbFramework
{
	/// <summary>
	/// Object relational mapper for entities.
	/// </summary>
	/// <typeparam name="EntityType">The type of entity to map to relational data.</typeparam>
	public class ObjectRelationalMapper<EntityType>
		where EntityType : class, new()
	{
		#region Data Members

		/// <summary>
		/// Factory provider for database communication.
		/// </summary>
		private IDbFactory _dbFactory;

		/// <summary>
		/// Info on the table the entity maps to.
		/// </summary>
		private TableAttribute _tableInfo;

		/// <summary>
		/// Command for performing selects on the relational data for the entity.
		/// </summary>
		private IDbCommand _selectCommand;

		/// <summary>
		/// Command for performing inserts on the relational data for the entity.
		/// </summary>
		private IDbCommand _insertCommand;

		/// <summary>
		/// Command for performing updates on the relational data for the entity.
		/// </summary>
		private IDbCommand _updateCommand;

		/// <summary>
		/// Command for performing deletes on the relational data for the entity.
		/// </summary>
		private IDbCommand _deleteCommand;

		/// <summary>
		/// Command for checking if the entity has a relational entry in the
		/// database.
		/// </summary>
		private IDbCommand _existsCommand;

		/// <summary>
		/// The relations defined on the entity that map to child entities.
		/// </summary>
		private Dictionary<RelationAttribute, PropertyInfo> _relationMappings;

		/// <summary>
		/// Mappings between the entity's properties and relational columns.
		/// </summary>
		private Dictionary<ColumnAttribute, PropertyInfo> _columnMappings;

		/// <summary>
		/// The column(s) that make up the primary key.
		/// </summary>
		private List<ColumnAttribute> _primaryKeyColumns;

		#endregion

		#region Constructor(s)

		/// <summary>
		/// Initialize the object-relational mapper.
		/// </summary>
		/// <param name="dbFactory">The database factory to use for communicating with the database.</param>
		public ObjectRelationalMapper(IDbFactory dbFactory)
		{
			if(dbFactory == null)
				throw new ArgumentNullException("dbFactory");
			_dbFactory = dbFactory;
			this.GetTableInfo();
			this.GetRelations();
			this.BuildCommands();
		}

		/// <summary>
		/// Gets the relation mapping information for parent-child relational data.
		/// </summary>
		private void GetRelations()
		{
			// Gather all relation mappings for the entity type
			Type entityType = typeof(EntityType);
			_relationMappings = new Dictionary<RelationAttribute, PropertyInfo>();
			BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance;
			foreach(PropertyInfo property in entityType.GetProperties(bindingFlags))
			{
				RelationAttribute[] relations = property.GetCustomAttributes(typeof(RelationAttribute), true) as RelationAttribute[];
				if(relations.Length > 0)
				{
					relations[0].DbFactory = _dbFactory;
					_relationMappings.Add(relations[0], property);
				}
			}
		}

		/// <summary>
		/// Gets the table mapping information.
		/// </summary>
		private void GetTableInfo()
		{
			Type entityType = typeof(EntityType);
			TableAttribute[] tableAttributes = entityType.GetCustomAttributes(typeof(TableAttribute), true) as TableAttribute[];
			if(tableAttributes.Length == 0)
				throw new InvalidProgramException(ErrorStrings.TableMappingRequired);
			_tableInfo = tableAttributes[0];
		}

		#endregion

		#region Command Builders

		/// <summary>
		/// Builds the select, insert, update, delete commands for the mapper.
		/// </summary>
		private void BuildCommands()
		{
			// Gather the property/column mappings defined on the entity
			Type entityType = typeof(EntityType);
			BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic;
			_columnMappings = new Dictionary<ColumnAttribute, PropertyInfo>();
			_primaryKeyColumns = new List<ColumnAttribute>();
			foreach(PropertyInfo property in entityType.GetProperties(bindingFlags))
			{
				// Get the column information the property should be mapped to
				ColumnAttribute[] columns = property.GetCustomAttributes(typeof(ColumnAttribute), false) as ColumnAttribute[];
				if(columns.Length == 0)
					continue;

				// If the column is part of the primary keys...
				if(columns[0].IsPrimaryKey)
					_primaryKeyColumns.Add(columns[0]);

				// Add the mapping
				_columnMappings.Add(columns[0], property);
			}

			// Validate that the mappings dictionary has:
			// + at least one primary key column
			// + a column mapping
			if(_columnMappings.Count == 0)
				throw new InvalidProgramException(ErrorStrings.ColumnMappingsNotFound);
			if(_primaryKeyColumns.Count == 0)
				throw new InvalidProgramException(ErrorStrings.PrimaryKeyMappingRequired);

			// Build the select, insert, update, and delete commands
			this.BuildSelectCommand();
			this.BuildInsertCommand();
			this.BuildUpdateCommand();
			this.BuildDeleteCommand();
			this.BuildExistsCommand();
		}

		/// <summary>
		/// Constructs the command that queries the database for the existance of the relational data
		/// for an entity.
		/// </summary>
		private void BuildExistsCommand()
		{
			StringBuilder criteriaPart = new StringBuilder();
			List<IDataParameter> paramList = new List<IDataParameter>();
			foreach(ColumnAttribute primaryKeyColumn in _primaryKeyColumns)
			{
				// Add parameter for the exists command
				IDataParameter param = _dbFactory.CreateParam(
					"@" + primaryKeyColumn.ColumnName,
					primaryKeyColumn.DbType,
					ParameterDirection.Input,
					DBNull.Value);
				paramList.Add(param);

				// Add column to criteria part of query
				criteriaPart.Append(string.Format("[{0}]=@{0}", primaryKeyColumn.ColumnName));
				criteriaPart.Append(",");
			}
			if(criteriaPart.Length > 0)
				criteriaPart.Remove(criteriaPart.Length - 1, 1);
			string commandTextFormat = "Select Count(*) From [{0}] Where {1}";
			string commandText = string.Format(commandTextFormat, _tableInfo.TableName, criteriaPart.ToString());
			_existsCommand = _dbFactory.CreateCommand(commandText);
			foreach(IDataParameter param in paramList)
				_existsCommand.Parameters.Add(param);
		}

		/// <summary>
		/// Constructs the delete command for deleting the relational data of an entity.
		/// </summary>
		private void BuildDeleteCommand()
		{
			StringBuilder criteriaPart = new StringBuilder();
			List<IDataParameter> paramList = new List<IDataParameter>();
			foreach(ColumnAttribute primaryKeyColumn in _primaryKeyColumns)
			{
				// Add parameter for the exists command
				IDataParameter param = _dbFactory.CreateParam(
					"@" + primaryKeyColumn.ColumnName,
					primaryKeyColumn.DbType,
					ParameterDirection.Input,
					DBNull.Value);
				paramList.Add(param);

				// Add column to criteria part of query
				criteriaPart.Append(string.Format("[{0}]=@{0}", primaryKeyColumn.ColumnName));
				criteriaPart.Append(",");
			}
			if(criteriaPart.Length > 0)
				criteriaPart.Remove(criteriaPart.Length - 1, 1);
			string commandTextFormat = "Delete From [{0}] Where {1}";
			string commandText = string.Format(commandTextFormat, _tableInfo.TableName, criteriaPart.ToString());
			_deleteCommand = _dbFactory.CreateCommand(commandText);
			foreach(IDataParameter param in paramList)
				_deleteCommand.Parameters.Add(param);
		}

		/// <summary>
		/// Constructs the update command for updating the relational data of the entity.
		/// </summary>
		private void BuildUpdateCommand()
		{
			StringBuilder fieldValueSetPart = new StringBuilder();
			StringBuilder criteriaPart = new StringBuilder();
			List<IDataParameter> paramList = new List<IDataParameter>();
			foreach(KeyValuePair<ColumnAttribute, PropertyInfo> kvp in _columnMappings)
			{
				ColumnAttribute columnInfo = kvp.Key;

				// Add parameter for the select command
				IDataParameter param = _dbFactory.CreateParam(
					"@" + columnInfo.ColumnName,
					columnInfo.DbType,
					ParameterDirection.Input,
					DBNull.Value);
				paramList.Add(param);

				// If the column is a primary key column, append it to the criteria 
				// section of the query
				if(columnInfo.IsPrimaryKey)
				{
					criteriaPart.Append(string.Format("[{0}]=@{0}", columnInfo.ColumnName));
					criteriaPart.Append(",");
				}

				// Otherwise append it to the field-value-set part of the query
				else
				{
					fieldValueSetPart.Append(string.Format("[{0}]=@{0}", columnInfo.ColumnName));
					fieldValueSetPart.Append(",");
				}
			}
			if(criteriaPart.Length > 0)
				criteriaPart.Remove(criteriaPart.Length - 1, 1);
			if(fieldValueSetPart.Length > 0)
				fieldValueSetPart.Remove(fieldValueSetPart.Length - 1, 1);

			// Construct the command
			string commandTextFormat = "Update [{0}] Set {1} Where {2}";
			string commandText = string.Format(commandTextFormat, _tableInfo.TableName, fieldValueSetPart.ToString(), criteriaPart.ToString());
			_updateCommand = _dbFactory.CreateCommand(commandText);
			foreach(IDataParameter param in paramList)
				_updateCommand.Parameters.Add(param);
		}

		/// <summary>
		/// Constructs the insert command to insert a new record for the relational data of
		/// the entity.
		/// </summary>
		private void BuildInsertCommand()
		{
			// Prepare the field list, value list, and create the params
			StringBuilder fieldList = new StringBuilder();
			StringBuilder valueList = new StringBuilder();
			List<IDataParameter> paramList = new List<IDataParameter>();
			foreach(KeyValuePair<ColumnAttribute, PropertyInfo> kvp in _columnMappings)
			{
				ColumnAttribute columnInfo = kvp.Key;

				// Add parameter for the insert command
				IDataParameter param = _dbFactory.CreateParam(
					"@" + columnInfo.ColumnName,
					columnInfo.DbType,
					ParameterDirection.Input,
					DBNull.Value);
				paramList.Add(param);

				// Append to field and value list
				fieldList.Append(string.Format("[{0}]", columnInfo.ColumnName));
				fieldList.Append(",");
				valueList.Append(string.Format("@{0}", columnInfo.ColumnName));
				valueList.Append(",");
			}
			if(fieldList.Length > 0)
				fieldList.Remove(fieldList.Length - 1, 1);
			if(valueList.Length > 0)
				valueList.Remove(valueList.Length - 1, 1);

			// Generate the command text
			string commandTextFormat = "Insert Into [{0}] ({1}) Values ({2})";
			string commandText = string.Format(commandTextFormat, _tableInfo.TableName, fieldList.ToString(), valueList.ToString());
			_insertCommand = _dbFactory.CreateCommand(commandText);
			foreach(IDataParameter param in paramList)
				_insertCommand.Parameters.Add(param);
		}

		/// <summary>
		/// Constructs the select command for the entity.
		/// </summary>
		private void BuildSelectCommand()
		{
			// Build the select command
			StringBuilder criteriaPart = new StringBuilder();
			List<IDataParameter> paramList = new List<IDataParameter>();
			SortedDictionary<int, string> sortList = new SortedDictionary<int, string>();
			foreach(KeyValuePair<ColumnAttribute, PropertyInfo> kvp in _columnMappings)
			{
				ColumnAttribute columnInfo = kvp.Key;
				if(columnInfo.IsPrimaryKey)
				{
					// Add parameter for the select command
					IDataParameter param = _dbFactory.CreateParam(
						"@" + columnInfo.ColumnName,
						columnInfo.DbType,
						ParameterDirection.Input,
						DBNull.Value);
					paramList.Add(param);

					// Add column to criteria part of query
					criteriaPart.Append(string.Format("[{0}]=@{0}", columnInfo.ColumnName));
					criteriaPart.Append(",");

					// If column is sortable, add to sort part of the query
					if(columnInfo is SortableColumnAttribute)
					{
						SortableColumnAttribute sortInfo = columnInfo as SortableColumnAttribute;
						string sortExp;
						if(sortInfo.Direction == SortDirection.Ascending)
							sortExp = columnInfo.ColumnName + " ASC";
						else
							sortExp = columnInfo.ColumnName + " DESC";
						sortList.Add(sortInfo.Ordinal, sortExp);
					}
				}
			}
			criteriaPart.Remove(criteriaPart.Length - 1, 1);
			string commandTextFormat = "Select * From [{0}] Where {1}";
			string commandText = string.Format(commandTextFormat, _tableInfo.TableName, criteriaPart.ToString());

			// If sort info exists, then append it to the select query
			if(sortList.Count > 0)
			{
				StringBuilder sortPart = new StringBuilder();
				foreach(KeyValuePair<int, string> kvp in sortList)
				{
					sortPart.Append(kvp.Value);
					sortPart.Append(",");
				}
				sortPart.Remove(sortPart.Length - 1, 1);
				commandText += " Order By " + sortPart.ToString();
			}

			// All information is now available to build the command
			_selectCommand = _dbFactory.CreateCommand(commandText);
			foreach(IDataParameter param in paramList)
				_selectCommand.Parameters.Add(param);
		}

		/// <summary>
		/// Fill's the given command's parameters with the values in the entity. The parameters
		/// defined in the command are used to determine the source of the values in the entity.
		/// </summary>
		/// <param name="command">The command to fill the parameters of.</param>
		/// <param name="entity">The entity to fill the values of the parameters with.</param>
		protected void FillCommandParams(IDbCommand command, EntityType entity)
		{
			foreach(IDataParameter param in command.Parameters)
			{
				foreach(KeyValuePair<ColumnAttribute, PropertyInfo> kvp in _columnMappings)
				{
					if("@" + kvp.Key.ColumnName == param.ParameterName)
					{
						object value = kvp.Value.GetValue(entity, null);
						if(value == null)
							value = DBNull.Value;
						param.Value = value;
						break;
					}
				}
			}
		}

		#endregion

		#region Select, Exists, Insert, Update, and Delete Methods For Entity

		/// <summary>
		/// Gets the relational data for the entity from the database. The primary key
		/// mappings defined in the entity are used to fetch the data from the database.
		/// </summary>
		/// <param name="entity">The entity to get the data for.</param>
		public void Select(EntityType entity)
		{
			this.FillCommandParams(_selectCommand, entity);
			DataSet resultSet = new DataSet();
			IDataAdapter adapter = _dbFactory.CreateAdapter(_selectCommand, null, null, null);
			adapter.Fill(resultSet);
			DataTable resultTable = resultSet.Tables[0];
			if(resultTable.Rows.Count == 0)
				throw new ArgumentException(ErrorStrings.EntityNotFound);
			DataRow resultRow = resultTable.Rows[0];
			foreach(KeyValuePair<ColumnAttribute, PropertyInfo> kvp in _columnMappings)
			{
				ColumnAttribute column = kvp.Key;
				PropertyInfo property = kvp.Value;
				if(!column.IsPrimaryKey)
					property.SetValue(entity, resultRow[column.ColumnName], null);
			}

			// If there are relations defined on the entity, then the child entities
			// are also retrieved.
			foreach(KeyValuePair<RelationAttribute, PropertyInfo> kvp in _relationMappings)
			{
				// Get the container to hold the child entities
				IList childEntities = kvp.Value.GetValue(entity, null) as IList;
				if(childEntities == null)
					throw new InvalidCastException(ErrorStrings.InvalidContainerTypeForChildEntities);
				childEntities.Clear();
				ConstructorInfo ormCtor = kvp.Key.ORMForRelatedEntityType.GetConstructor(Type.EmptyTypes);
				object orm = ormCtor.Invoke(new object[] { _dbFactory });
			}
		}

		/// <summary>
		/// Queries the database for the existance of a row mapped to the entity.
		/// </summary>
		/// <param name="entity">The entity to check for the existence of.</param>
		/// <returns>True if a row exists for the entity; false otherwise.</returns>
		public bool Exists(EntityType entity)
		{
			this.FillCommandParams(_existsCommand, entity);
			return (int)_existsCommand.ExecuteScalar() == 1;
		}

		/// <summary>
		/// Inserts a new record for the entity in the database.
		/// </summary>
		/// <param name="entity">The entity to insert a record for.</param>
		public void Insert(EntityType entity)
		{
			this.FillCommandParams(_insertCommand, entity);
			_insertCommand.ExecuteNonQuery();
		}

		/// <summary>
		/// Updates an existing record of an entity in the database.
		/// </summary>
		/// <param name="entity">The entity to update the record for.</param>
		public void Update(EntityType entity)
		{
			this.FillCommandParams(_updateCommand, entity);
			_updateCommand.ExecuteNonQuery();
		}

		/// <summary>
		/// Deletes an existing record of an entity in the database.
		/// </summary>
		/// <param name="entity">The entity to delete the record of.</param>
		public void Delete(EntityType entity)
		{
			this.FillCommandParams(_deleteCommand, entity);
			_deleteCommand.ExecuteNonQuery();
		}

		#endregion

		#region Fill From/To Data Table

		/// <summary>
		/// Loads the entities by reading from the mapped table.
		/// </summary>
		/// <param name="table">The table to load from.</param>
		/// <returns>An array of entities, one for each row in the table.</returns>
		public EntityType[] LoadFromTable(DataTable table)
		{
			if(table == null)
				throw new ArgumentNullException("table");

			Type entityType = typeof(EntityType);
			ConstructorInfo entityConstructor = entityType.GetConstructor(Type.EmptyTypes);
			EntityType[] entities = new EntityType[table.Rows.Count];
			for(int index = 0; index < table.Rows.Count; index++)
			{
				DataRow row = table.Rows[index];
				object entity = entityConstructor.Invoke(null);
				foreach(KeyValuePair<ColumnAttribute, PropertyInfo> kvp in _columnMappings)
				{
					ColumnAttribute column = kvp.Key;
					PropertyInfo property = kvp.Value;
					object value;
					if(row[column.ColumnName] == DBNull.Value)
						value = null;
					else
						value = row[column.ColumnName];
					property.SetValue(entity, value, null);
				}
				entities[index] = entity as EntityType;
			}
			return entities;
		}

		/// <summary>
		/// Creates a data table with the data in the list of entities.
		/// </summary>
		/// <param name="entities">The entities containing the data to fill the table with.</param>
		/// <returns>Data table with the data from the entities.</returns>
		public DataTable GetTable(IEnumerable<EntityType> entities)
		{
			// paranoia
			if(entities == null)
				throw new ArgumentNullException("entities");

			// construct the data table schema
			DataTable table = new DataTable();
			List<DataColumn> pkCols = new List<DataColumn>();
			foreach(KeyValuePair<ColumnAttribute, PropertyInfo> kvp in _columnMappings)
			{
				ColumnAttribute columnAttr = kvp.Key;
				DataColumn column = new DataColumn(columnAttr.ColumnName);
				table.Columns.Add(column);
				if(columnAttr.IsPrimaryKey)
					pkCols.Add(column);
			}
			table.PrimaryKey = pkCols.ToArray();

			// fill the data table
			Type entityType = typeof(EntityType);
			foreach(EntityType entity in entities)
			{
				DataRow row = table.NewRow();
				foreach(KeyValuePair<ColumnAttribute, PropertyInfo> kvp in _columnMappings)
				{
					ColumnAttribute columnAttr = kvp.Key;
					PropertyInfo property = kvp.Value;
					row[columnAttr.ColumnName] = property.GetValue(entity, null);
				}
				table.Rows.Add(row);
			}
			return table;
		}

		/// <summary>
		/// Updates the underlying database with the data in the table.
		/// </summary>
		/// <param name="table">The table containing the data to update the database with.</param>
		public void UpdateTable(DataTable table)
		{
			IDataAdapter adapter = _dbFactory.CreateAdapter(null, _insertCommand, _updateCommand, _deleteCommand);
			DataSet dataSet = new DataSet();
			dataSet.Tables.Add(table);
			adapter.Update(dataSet);
		}

		#endregion
	}
}